Technical Overview – Hinshaw Mortgage

Project Overview

The goal of this project was to recreate the full analytics environment I worked with at Rocket Mortgage—without using any real customer or operational data. Mortgage data is highly sensitive, tightly regulated, and cannot be shared outside the company, which meant I needed a way to demonstrate my technical skills, domain knowledge, and end‑to‑end workflow without exposing proprietary information. The solution was to engineer a fully synthetic mortgage pipeline that mirrors the structure, behavior, and complexity of a real lending operation.

To accomplish this, I designed a fictional mortgage company—Hinshaw Mortgage—and generated three core datasets in Python: a loan pipeline, an extension dataset, and an employee roster. These datasets were intentionally modeled after the types of data sources I worked with in production: multi‑table relationships, operational metrics, timeline‑driven events, and performance indicators that require transformation before they become meaningful.

Once the synthetic data was created, I built a complete analytics pipeline around it—cleaning and transforming the data in Jupyter Notebook, loading it into PostgreSQL, modeling relationships between tables, and connecting the final structure to Power BI. From there, I developed more than fifty custom DAX measures to replicate the KPI logic, scoring systems, and operational insights used in real mortgage analytics. The result is a set of dashboards that behave like the tools used inside a mortgage company, but built entirely on safe, synthetic data.

This Technical Overview page documents the full engineering process behind the dashboards: how the data was generated, how the ETL workflow was structured, how the SQL model was designed, how the Power BI measures were built, and how the final project was deployed as a public portfolio site. It serves as the technical backbone of the Hinshaw Mortgage project and demonstrates the complete analytics workflow—from raw data creation to polished, interactive reporting.

1. Synthetic Data Generation (Python)

Using Jupyter Notebook as the development environment, I built a Python script that generates the synthetic mortgage datasets step‑by‑step, mirroring the structure and behavior of real production data. The script uses a combination of Pandas for dataframe construction, NumPy for statistical distributions, random for controlled randomness, and Python’s datetime library to simulate realistic timelines such as application dates, lock periods, and funding lags.

The data generation script produces three core datasets: Employees, Loans, and Loan Extensions. Each dataset is created using domain rules, weighted randomness, and capped normal distributions that reflect real mortgage operations. The script is structured in three major sections:

1. Employee Dataset — The script begins by defining a role configuration that determines how many employees exist in each position (loan officers, processors, underwriters, account executives, and leadership roles). Using loops and weighted selections, each employee is assigned:

  • A randomly selected first and last name
  • A branch and region pulled from a predefined branch list
  • A hire date generated within a realistic 10-year window
  • A compensation structure based on role (salary vs. bps)
  • A unique employee ID

A helper function retrieves employee IDs by role, allowing the loan generation process to assign each loan to a realistic team structure. This mirrors how real mortgage companies organize production teams.

2. Loan Pipeline Dataset — The script then generates 10,000 synthetic loans. Each loan is built using logic that reflects real mortgage behavior:

  • Application and lock dates are generated using date offsets from a base date to simulate a rolling pipeline.
  • Loan amounts are drawn from NumPy normal distributions that vary by state, producing realistic price differences between markets like CA, TX, and NC.
  • Product type is determined by loan amount (e.g., jumbo if above the conforming limit) or by weighted random selection.
  • Credit metrics (FICO, DTI, CLTV) use capped normal distributions to mimic real borrower profiles while enforcing realistic minimums and maximums.
  • Loan status (Funded, Withdrawn, Denied, etc.) is assigned using weighted probabilities to reflect real pull‑through behavior.
  • Employee assignments link each loan to a loan officer, processor, underwriter, and (if wholesale) an account executive.
  • Compensation is calculated using the loan amount and a randomized bps value tied to the loan officer’s comp plan.

This produces a loan pipeline that looks and behaves like the datasets used inside a real mortgage company—complete with production variation, channel differences, and realistic borrower characteristics.

3. Extension Dataset — For loans that fund, the script determines whether extensions occur based on product type, purpose, and operational randomness. Each extension record includes:

  • The number of extension days (5 or 15, depending on sequence)
  • The fee percentage and fee amount, calculated from the loan amount
  • A reason code (borrower delay, appraisal delay, underwriting conditions, etc.)
  • The request date and before/after lock expiration dates
  • The employee who approved the extension

The extension logic uses sequential loops to update expiration dates, apply fee rules, and generate realistic extension patterns. Early extensions tend to be shorter and cheaper, while later extensions carry higher cost and longer durations, mirroring real lock‑management behavior.

By combining statistical modeling, domain rules, helper functions, and controlled randomness, the final datasets behave like real mortgage data while remaining completely synthetic. This provides a safe, realistic foundation for the SQL modeling, DAX measures, and dashboards that follow.

2. ETL & Data Cleaning (Jupyter Notebook)

After generating the synthetic datasets, I used Jupyter Notebook to perform all ETL and data‑cleaning steps before loading anything into a database. This stage focused entirely on preparing the raw CSV outputs for analytical use by applying a structured Extract → Transform → Load workflow using Pandas for data manipulation and NumPy for numeric operations.

Extract — I began by loading the raw CSV files (employees.csv, loans.csv, loan_extensions.csv) into Pandas dataframes. Using .head(), .info(), and .columns, I validated row counts, inspected data types, identified missing values, and confirmed the structure of each dataset. This ensured the generated data aligned with the expected schema before any transformations were applied.

Transform — Each dataset required extensive cleaning and standardization to prepare it for relational modeling and Power BI. Key transformations included:

  • Column normalization: Standardized column names by stripping whitespace, converting to lowercase, replacing spaces with underscores, and then renaming to business‑friendly labels.
  • Date parsing: Converted all date fields (hire dates, application dates, lock dates, funding dates, extension dates) using pd.to_datetime() with error coercion.
  • Numeric conversion: Coerced salary, BPS compensation, loan amounts, interest rates, extension fees, and other numeric fields into proper numeric types.
  • Text cleaning: Standardized categorical fields such as extension reasons and requestor roles by stripping whitespace, normalizing spacing, and applying consistent capitalization.
  • Feature engineering: Added new fields including employee tenure, compensation flags, branch labels, total extension days, total extension cost, and extension buckets.
  • Duplicate checks: Verified dataset integrity using .duplicated() across full rows and key combinations (e.g., loan number + extension sequence).
  • Missing value handling: Identified and resolved null values where appropriate, ensuring all required fields were populated for downstream modeling.

These transformations ensured that each dataset was clean, consistent, and aligned with real mortgage data structures. For example, employee compensation fields were mutually exclusive (salary vs. BPS), loan dates were properly typed for timeline calculations, and extension records included aggregated metrics such as total extension days and total extension cost per loan.

Once the datasets were fully cleaned and standardized, I exported them to CSV. These cleaned files served as the source inputs for the SQL modeling phase.

3. SQL Modeling (PostgreSQL Star Schema)

With the cleaned datasets prepared, I designed a dimensional data model in PostgreSQL to support analytical querying and Power BI reporting. The model follows a star schema—the standard structure used in enterprise BI systems—providing clean relationships, predictable grain, and efficient performance for analytical workloads.

The schema centers around a primary fact table (loans_clean) supported by a dimension table (employees_clean) and a secondary fact table (extensions_clean). I created each table using explicit CREATE TABLE statements, defining data types, primary keys, and foreign key constraints to enforce relational integrity.

Fact Table: loans_clean — This table represents the core grain of the model: one row per mortgage loan. It includes application timelines, borrower credit metrics, product details, compensation calculations, and employee assignments. The primary key is loan_number, and the table contains foreign keys linking to employee roles such as loan officer, processor, underwriter, and account executive.

Dimension Table: employees_clean — This table stores employee attributes including name, role, region, branch, compensation structure, and tenure. The primary key is employee_id. This dimension supports multiple relationships back to the loans fact table, enabling analysis by loan officer, processor, underwriter, or account executive.

Secondary Fact Table: extensions_clean — This table captures lock extension activity at the loan level. Each row represents a single extension event, including extension days, fees, reason codes, and before/after expiration dates. The primary key is extension_id, and the table includes a foreign key linking back to loan_number in the loans fact table. This creates a one‑to‑many relationship that mirrors real mortgage lock‑management workflows.

To implement the schema, I used SQLAlchemy and psycopg2 to connect to PostgreSQL and execute the table‑creation statements. Each table was defined with appropriate data types (e.g., DATE, NUMERIC, INT, TEXT) to ensure consistency and enforce schema integrity. After creating the tables, I used Pandas’ to_sql() method to load the cleaned dataframes into the database.

Once the data was loaded, I validated the model by running SQL queries directly from the notebook—such as SELECT COUNT(*)—to confirm row counts and verify that foreign key relationships were preserved. This relational structure provides a stable foundation for Power BI, enabling efficient joins, accurate aggregations, and a clean semantic layer for DAX measures.

The result is a fully functional star schema that mirrors the data modeling practices used in real mortgage analytics environments, supporting both operational reporting and deeper performance analysis.

4. Connecting Power BI to PostgreSQL

With the star schema fully implemented in PostgreSQL, the next step was to connect the data model to Power BI. I chose Power BI for this project because I have more than three years of experience building operational dashboards at Rocket Mortgage, where I used it daily to support leadership reporting, performance tracking, and real‑time operational insights. Re‑creating that workflow in this portfolio project allowed me to demonstrate the same end‑to‑end analytics process I use in production without sharing sensitive company data.

Power BI connects natively to PostgreSQL, so I used the built‑in PostgreSQL connector to establish a direct connection to the Hinshaw_Mortgage database. After entering the server, database name, and authentication details, Power BI detected the three modeled tables— employees_clean, loans_clean, and extensions_clean—exactly as they were defined in SQL.

Once connected, I selected the tables and loaded them into Power BI. Because the schema was already structured as a star model, Power BI automatically recognized the primary and foreign key relationships between the tables. This created a clean semantic layer where the loans_clean fact table sits at the center, with employees_clean and extensions_clean acting as supporting dimension and secondary fact tables.

This relational structure allowed me to begin building DAX measures immediately—without needing to reshape or re‑model the data inside Power BI. The result is a reporting environment that mirrors the same architecture I used at Rocket Mortgage: a SQL‑backed star schema feeding a set of interactive, operational dashboards built in Power BI.

The connection step completed the data pipeline, enabling the dashboards to refresh directly from PostgreSQL and ensuring that all measures, filters, and drill‑downs operate on a clean, well‑modeled dataset.

5. DAX Measures & Python Analytics

With the PostgreSQL star schema connected to Power BI, the next step was to build the analytical layer that powers the dashboards. I have more than three years of experience using Power BI at Rocket Mortgage, where I built operational dashboards for leadership, performance management, and pipeline monitoring. This project mirrors that same workflow: using DAX to engineer business logic, create performance metrics, and shape the semantic layer that drives the visuals.

Performance Score (DAX + Python)

The core metric in this project is the Loan Officer Performance Score, a composite measure designed to evaluate loan officers on both production and operational discipline. Rather than ranking loan officers solely by funded volume, closed loans or another solitary metric, the score blends four key components:

  • Funded Rate — measures conversion efficiency and pipeline quality
  • Fallout Rate — penalizes volatility and poor borrower pull‑through
  • Closed Loan Volume — rewards sustainable production
  • Operational Efficiency — incorporates cycle‑time and extension behavior

These components are combined in DAX using a weighted formula that rewards consistent, high‑quality production while penalizing excessive fallout or operational friction. The result is a single, interpretable score that reflects both how much a loan officer produces and how efficiently they manage their pipeline.

Once the score is calculated in DAX, I use Python inside Power BI to generate a quadrant visualization. The script dynamically computes the median fallout rate and median performance score, then classifies each loan officer into one of four categories:

  • Top Performer — high score, low fallout
  • Volatile — high score, high fallout
  • High Risk — low score, high fallout
  • Underperformer — low score, low fallout

Bubble size is scaled by closed‑loan volume, allowing high‑impact producers to stand out visually. This hybrid DAX + Python approach mirrors the advanced analytics workflows I used at Rocket Mortgage, where composite scoring models and quadrant analysis were used to identify coaching opportunities, operational bottlenecks, and top‑performing loan officers.

Extension Performance Score (DAX)

I also developed a second composite metric: the Extension Performance Score. This measure evaluates how efficiently each loan officer manages lock extensions by combining four components:

  • Loan Officer Extension % — the share of funded loans requiring extensions
  • Total Extensions (Company + Borrower) — operational burden created by each loan officer
  • Avg Cost Per Extension Day — financial impact of delays
  • Closed Loans — production volume, included as a positive scaling factor

The raw score is normalized by dividing it by the maximum score across all loan officers, ensuring the final result is a clean 0–100 scale. This makes the metric intuitive and comparable across branches, channels, and time periods.

Company‑Caused vs Borrower‑Caused Extensions

A key enhancement in this project was separating extensions into company‑caused and borrower‑caused categories. Using DAX filters on the extension reason codes, I created measures that isolate operational delays (e.g., underwriting conditions, appraisal delays) from borrower‑driven delays (e.g., missing documents, rate shopping). This additional layer of segmentation provides deeper insight into operational efficiency and coaching opportunities.

These filtered measures feed into KPIs, trend lines, and branch‑level comparisons, allowing leaders to quickly identify whether extension behavior is driven by internal process issues or borrower behavior.

Percentage Measures & Operational KPIs

I built a full suite of percentage‑based DAX measures to support the dashboards, including:

  • Extension Percentage — extensions ÷ funded loans
  • Close Percentage — funded loans ÷ total applications
  • Fallout Rate — fallout loans ÷ total applications
  • Borrower vs Company Extension % — segmented extension behavior
  • Month‑over‑Month Extension Change — operational trend tracking
  • Pct of Total Extension Cost — cost attribution across branches and loan officers

These measures form the backbone of the dashboards, enabling dynamic filtering by loan officer, branch, region, channel, product type, and month.

Cycle‑Time & Risk Segmentation Measures

To deepen the operational analysis, I added several supporting measures that mirror real mortgage KPIs:

  • Days to Lock — measures borrower engagement speed
  • Days Lock to Funding — measures operational cycle time
  • CLTV / FICO / DTI Groupings — risk segmentation for funded loans
  • Loans With 3+ Extensions — high‑risk operational flag

These measures help identify bottlenecks, risk patterns, and coaching opportunities across branches and loan officers.

Leaderboard & Benchmarking Measures

To support performance comparisons, I built a set of leaderboard and benchmarking measures, including:

  • Top Producer Name and Top Producer Closed Loans
  • Highest / Lowest Extension % by branch and loan officer
  • Highest / Lowest Fallout % across the company
  • Highest Funded Volume and Highest Volume Loan Officer

These measures power the “Top Performer” and “Lowest Performer” cards throughout the dashboards, giving leadership a quick read on who is excelling and who may need support.

Conditional Formatting & Visual Enhancements

To make the dashboards more intuitive, I applied conditional formatting to key measures such as extension percentage, fallout rate, and performance scores. High‑risk values are highlighted in red, strong performance in green, and neutral values in gold or blue. This visual language mirrors the operational dashboards I built at Rocket Mortgage, where color‑coded KPIs help leaders identify issues at a glance.

Combined with the Python quadrant visual, these enhancements create a reporting experience that is both analytical and highly actionable.

Together, the DAX measures, composite scoring models, and Python‑powered visuals form the analytical engine of the dashboards, enabling deep performance insights across loan officers, branches, and operational processes.

7. User Workflow & How the Dashboards Are Used

Each dashboard in this project was designed with a clear user workflow in mind. Rather than creating static visuals, the goal was to build an interactive reporting experience that mirrors how real mortgage operations teams explore data, diagnose issues, and make decisions. The dashboards guide users from high‑level trends down to granular, loan‑officer‑level insights through intuitive filtering, consistent navigation, and a clean visual hierarchy.

1. Start with the Pipeline Insights Dashboard

Most users begin on the Pipeline Insights page to understand the overall health of the business. This dashboard answers foundational questions such as:

  • How is our close rate trending?
  • Which branches are driving funded volume?
  • Where is fallout increasing?
  • How do borrower credit metrics compare across branches?

Users can filter by branch, channel, product type, or month to isolate performance patterns. This page acts as the “executive summary” before diving deeper into loan officer or extension behavior.

2. Drill into Loan Officer Performance

After reviewing pipeline trends, users typically move to the Loan Officer Performance Deep‑Dive. This dashboard is designed for branch leaders, sales managers, and performance coaches who need to understand:

  • Which loan officers are top performers?
  • Who has high fallout or volatility?
  • How do cycle‑time metrics compare across the team?
  • Which loan officers need coaching or support?

The Python quadrant visualization provides an immediate snapshot of performance quality and consistency. Users can click on any loan officer to filter the entire page, updating KPIs, trend lines, and the performance matrix in real time.

3. Analyze Extension Behavior & Cost Drivers

The Extension Deep‑Dive dashboard is used when leaders want to understand operational friction and cost. This page answers questions such as:

  • Are extensions increasing or decreasing over time?
  • Which branches or loan officers drive the most extension cost?
  • Are delays caused by the company or the borrower?
  • Which extension reasons are most common?
  • Where do we see high‑risk patterns like 3+ extensions?

Users can drill into specific branches or loan officers to see extension performance scores, cost per loan, and detailed reason breakdowns. This workflow mirrors real lock‑desk and operations reviews, where identifying the root cause of delays is critical.

4. Compare Branches & Loan Officers Across Dashboards

Because all dashboards share the same star‑schema model and DAX measures, users can seamlessly compare performance across pages. Selecting a branch or loan officer on one dashboard automatically filters the others, enabling:

  • Branch‑to‑branch comparisons
  • Loan officer benchmarking
  • Channel‑specific performance analysis
  • Product‑level segmentation

This interconnected workflow allows leadership to move from high‑level trends to root‑cause analysis in just a few clicks.

5. A User Experience Designed for Real Operations

The dashboards were intentionally designed to feel familiar to anyone who has worked in mortgage operations. Clean KPI cards, intuitive color coding, matrix tables, and dual‑axis visuals make it easy to interpret complex metrics quickly. The custom Hinshaw Mortgage logo adds a polished, branded feel that ties the experience together.

The result is a reporting suite that not only showcases technical skill in Power BI, DAX, SQL, and Python, but also reflects real mortgage domain expertise and an understanding of how operational teams use data to drive decisions.

7. Portfolio Website Deployment

To showcase this project in a way that reflects the full depth of the work, I built and deployed a custom portfolio website using GitHub Pages. Rather than presenting the dashboards as static PDFs—which reveal almost nothing about the underlying process—I wanted a platform that allowed me to walk through the entire analytics pipeline: data generation, ETL, SQL modeling, DAX engineering, Python visuals, and dashboard design.

I developed the site using HTML for structure and CSS for styling, giving me complete control over layout, spacing, typography, and the overall visual identity. This also allowed me to embed screenshots, code snippets, and technical explanations directly alongside the dashboards, creating a cohesive narrative that mirrors how real analytics projects are documented internally.

The site is hosted through GitHub Pages, which provides a clean, reliable, and version‑controlled deployment pipeline. Any updates to the project—new dashboards, refined DAX measures, or additional documentation—can be pushed directly from the repository and published instantly.

I also created a custom logo for Hinshaw Mortgage to give the site and dashboards a polished, branded feel. While a small detail, it reinforces the idea that this project was built with the same care and professionalism as a real internal analytics tool.

The end result is a fully interactive, publicly accessible portfolio site that not only displays the dashboards, but also explains the reasoning, modeling decisions, and technical work behind them. This format provides far more transparency and insight than a PDF ever could, and it allows hiring managers or stakeholders to understand both the outputs and the thinking that produced them.

8. Technology Stack

This project was built using a modern, end‑to‑end analytics stack that mirrors real enterprise BI workflows. Each tool played a specific role in the pipeline—from data generation and transformation to modeling, visualization, and deployment.

  • Python (Pandas, NumPy, Matplotlib) — used for data generation, ETL, feature engineering, and the Python quadrant visualization inside Power BI.
  • Jupyter Notebook — served as the development environment for all ETL work, including data cleaning, transformation, validation, and loading the cleaned datasets into PostgreSQL.
  • PostgreSQL — served as the relational database for the star schema, enabling clean modeling, referential integrity, and fast analytical queries.
  • SQLAlchemy + psycopg2 — handled database connections and table creation, allowing Python to interface directly with PostgreSQL.
  • Power BI — used to build all dashboards, DAX measures, KPIs, matrix tables, and interactive visuals.
  • DAX — powered all business logic, including composite scoring models, extension segmentation, cycle‑time metrics, and percentage‑based KPIs.
  • GitHub Pages — hosted the portfolio website, providing a clean and version‑controlled deployment pipeline.
  • HTML & CSS — used to design and style the website, allowing full control over layout, typography, and the presentation of dashboards and documentation.
  • Custom Branding — created a custom Hinshaw Mortgage logo to give the dashboards and site a polished, professional identity.

Together, these tools form a complete analytics ecosystem—from raw data to production‑ready dashboards—demonstrating both technical proficiency and the ability to design realistic, scalable BI solutions.